﻿Imports System.Data.OleDb
Imports System.IO
Imports System.IO.File
Imports System.Xml

Module 全局代码

    Public apps As String = Environment.CurrentDirectory.ToString   '程序所在目录路径

    '用户信息
    Public AccountName As String    '用户名
    Public WriteRight As Boolean    '修改权限
    Public ReadRight As Boolean     '查看权限
    Public ManageRight As Boolean   '管理权限

    '数据库连接
    Public cnn As OleDbConnection
    '连接字符串
    Public New_dbConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + apps + "\data\db.mdb;;Jet OLEDB:Database Password=665752;"
    Public Old_dbConnectionString As String
    Public Old_dbfileName As String
    '数据库设置
    Public DBchoose As String

    '窗体变量
    Public PinPaiform As 品牌型号           '品牌型号
    Public CommoditIn As 入货管理           '入货管理
    Public CommoMulIn As 批量入货           '批量入货
    Public Commodsell As 出售管理           '出售管理

    Public PWchangeFM As 修改密码           '修改密码
    Public AccountMFM As 用户管理           '用户管理

    Public SearchForm As 查看库存           '查看库存
    Public LogSearchF As 出售记录           '出售记录

    Public CalulaForm As 结算窗体           '出售记录
    Public RecordDelF As 记录删除           '记录删除

    Public StarRpForm As 维修录入           '维修录入
    Public ResuRpForm As 维修结果           '维修结果
    Public SettRPForm As 参数设置           '维修设置
    Public PrRecoForm As 维修记录           '维修记录

    Public SelsRsForm As 订单记录           '订单记录   
    Public OldBbSForm As 旧数据库           '订单记录   


#Region "功能代码"

    '建立数据库连接
    Public Function getAccessConnection(cnstr As String) As OleDbConnection

        '连接字符串
        Dim dbConnectionString As String = cnstr
        'Dim dbConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + apps + "\data\db.mdb;Persist Security Info=True"

        '建数据库连接
        Dim dbConnection As OleDbConnection = New OleDbConnection(dbConnectionString)

        Try
            dbConnection.Open()
        Catch Ex As Exception
            MsgBox(Ex.Message.ToString)
        End Try

        Return dbConnection

    End Function

    '图片按扭外观变化
    Public Sub ButtonPic(ByVal pic As PictureBox, ByVal picfile As String)
        Dim str1 As String = apps + "\skin\" + picfile
        pic.ImageLocation = str1
    End Sub

    'SQL字符生成记录集
    Public Function SQLtoDataSet(ByVal sqlstr As String) As DataSet
        '数据库适配器
        Dim DataAdapter As New OleDbDataAdapter
        Dim dataSet As New DataSet
        DataAdapter.SelectCommand = New OleDbCommand(sqlstr, cnn)
        DataAdapter.Fill(dataSet)
        DataAdapter.Dispose()
        Return dataSet

    End Function

    'SQL字符修改数据库
    Public Sub SQLupdata(ByVal sqlstr As String)
        Dim MyCommand As New OleDb.OleDbCommand(sqlstr, cnn)
        MyCommand.ExecuteNonQuery()
        MyCommand.Dispose()
    End Sub

    '判断数据库中是否己存在记指定记录
    Public Function RecordSetExist(ByVal table As String, ByVal field As String, ByVal value As String)
        Dim strtem As String = "select * from " + table + " where " + field + " = '" + value + "'"
        Dim temset As Data.DataSet = SQLtoDataSet(strtem)
        If temset.Tables(0).Rows.Count = 0 Then
            Return False
        Else
            Return True
        End If
    End Function

    '查库存
    Public Function sql_KC(ByVal PP As String, ByVal XH As String) As Integer

        Dim RH As Integer = 0
        Dim CS As Integer = 0

        '计算总入货量
        Dim sqlstr1 As String = "select 品牌,型号,数量,备注2 from 入货表 where 品牌='" + PP + "' and 型号 ='" + XH + "' and 备注2 is null "
        Dim ds1 As Data.DataTable = SQLtoDataSet(sqlstr1).Tables(0)
        If ds1.Rows.Count > 0 Then
            For i = 0 To ds1.Rows.Count - 1
                RH += ds1.Rows(i).Item("数量")
                Debug.Print("备注2" + ds1.Rows(i).Item("备注2").ToString)
            Next
        End If


        '计算总出售量
        Dim sqlstr2 As String = "select 品牌,型号,售出数量,备注2 from 出售表 where 品牌='" + PP + "' and 型号 ='" + XH + "' and 备注2 is null "
        Dim ds2 As Data.DataTable = SQLtoDataSet(sqlstr2).Tables(0)
        If ds2.Rows.Count > 0 Then
            For i = 0 To ds2.Rows.Count - 1
                CS += ds2.Rows(i).Item("售出数量")
            Next
        End If

        Return RH - CS

    End Function

    '金额小写转大写
    Public Function RMBtoBig(ByVal ZE As Integer) As String
        If ZE = 0 Then
            Return "零元整"
        ElseIf ZE > 0 Then
            Dim w As Integer = ZE.ToString.Length
            Dim DX As String = "元整"


            Dim flag2 As Boolean = True               '低位非零标志
            Dim flag3 As Integer = 0                      '目前位指针（右起）
            Dim flag4 As Boolean = False                   '中间连零标志（true为己出现0）


            For i = w To 1 Step -1
                flag3 += 1

                Dim str1 As String = Strings.Mid(ZE.ToString, i, 1)    '识别位指针

                If str1 <> "0" Then
                    flag2 = False
                    flag4 = False
                    DX = RMBtoBig_si(str1) + RMBtoBig_bi(flag3) + DX
                ElseIf str1 = "0" And Not flag2 Then
                    If Not flag4 Then
                        flag4 = True
                        DX = RMBtoBig_si(str1) + DX
                    End If

                End If




            Next


            Return DX
        Else
            Return "金额数据出错！"
        End If

    End Function
    Public Function RMBtoBig_si(ByVal XX As String) As String
        Select Case XX
            Case "1"
                Return "壹"
            Case "2"
                Return "贰"
            Case "3"
                Return "叁"
            Case "4"
                Return "肆"
            Case "5"
                Return "伍"
            Case "6"
                Return "陆"
            Case "7"
                Return "柒"
            Case "8"
                Return "捌"
            Case "9"
                Return "玫"
            Case "0"
                Return "零"
            Case Else
                Return "Erro"
        End Select
    End Function
    Public Function RMBtoBig_bi(ByVal i As Integer) As String
        Select Case i
            Case 1
                Return ""
            Case 2
                Return "拾"
            Case 3
                Return "佰"
            Case 4
                Return "仟"
            Case 5
                Return "万"
            Case 6
                Return "拾万"
            Case 7
                Return "佰万"
            Case 8
                Return "仟万"
            Case 9
                Return "亿"
            Case Else
                Return "过亿"
        End Select
    End Function

    '创建TitleSet类型，用于读取设置txt设置文件
    Public Class TitleSet
        Public T1, T2, T3 As String
        Public T1x, T1y, T2x, T2y, T3x, T3y As Integer

    End Class

    '读取txt设置文件（data\SEL(出售单）和REP（维修单）
    Public Function ReadTx(ByVal Str As String) As TitleSet

        Dim ResultX As New TitleSet
        Dim FileNaM As String = apps + "\data\" + Str
        Dim ConStr As String = IO.File.ReadAllText(FileNaM)
        Dim ConStrs() As String = ConStr.Split("|")

        ResultX.T1 = ConStrs(0)
        ResultX.T1x = Int(ConStrs(1))
        ResultX.T1y = Int(ConStrs(2))

        ResultX.T2 = ConStrs(3)
        ResultX.T2x = Int(ConStrs(4))
        ResultX.T2y = Int(ConStrs(5))

        ResultX.T3 = ConStrs(6)
        ResultX.T3x = Int(ConStrs(7))
        ResultX.T3y = Int(ConStrs(8))

        Return ResultX


    End Function

    '读取设置文件
    Public Sub ReadSettings()

        '设置文件位置
        Dim fileS As String = apps + "\data\settings.xml"
        'xml文件对象
        Dim xd As New XmlDocument
        '如果文件不存在，则创建文件
        If File.Exists(fileS) = False Then
            '创建第一行描述信息
            Dim dec As XmlDeclaration = xd.CreateXmlDeclaration("1.0", "utf-8", "")
            xd.AppendChild(dec)
            '创建根节点
            Dim Root As XmlElement = xd.CreateElement("Settings")
            xd.AppendChild(Root)
            '创建记录设置信息的节点
            Dim DBs As XmlElement = xd.CreateElement("OldDBFile")
            DBs.InnerText = "NotSet"
            Root.AppendChild(DBs)
            '保存文件
            xd.Save(fileS)

        Else
            '读取文件
            xd.Load(fileS)
            '获得根节点
            Dim Root As XmlElement = xd.DocumentElement
            '获得子节点
            Dim SetNodes As XmlNodeList = Root.ChildNodes
            Dim Tnode As XmlNode

            For i = 0 To SetNodes.Count - 1
                If SetNodes.Item(i).Name = "OldDBFile" Then
                    Tnode = SetNodes.Item(i)
                    Exit For
                End If
            Next


            If Tnode.InnerText <> "NotSet" And Tnode IsNot Nothing Then
                Old_dbConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Tnode.InnerText + ";;Jet OLEDB:Database Password=665752;"
                Old_dbfileName = Tnode.InnerText
            Else
                Old_dbConnectionString = "NotSet"
                Old_dbfileName = "NotSet"
            End If

        End If
    End Sub


#End Region

End Module
